Oracle批量、大量Update方法总结 | 您所在的位置:网站首页 › oracle update批量提交 › Oracle批量、大量Update方法总结 |
一、业务场景: (1)主从两个表,主表Student,有字段id、name、sex,从表Boy,有字段id、name,主从表同一对象id相同 (2)从表Boy的name属性被业务修改,定时批量处理主表,以维持主表name属性与从表一致
二、表结构 1、主表 Student 2、从表 Boy
三、建表SQL(DDL) 1、主表 Student -- DDL CREATE TABLE student ( id NUMBER NOT NULL , name VARCHAR2(255 BYTE) NULL , sex VARCHAR2(255 BYTE) NULL ) ALTER TABLE student ADD CHECK (id IS NOT NULL); -- DML INSERT INTO student VALUES ('1', 'zhangsan', 'boy'); INSERT INTO student VALUES ('2', 'lisi', 'girl'); INSERT INTO student VALUES ('3', 'wangwu', 'boy');2、从表 Boy -- DDL CREATE TABLE boy ( id NUMBER NOT NULL , name VARCHAR2(255 BYTE) NULL ) -- DML INSERT INTO boy VALUES ('1', '张三'); INSERT INTO boy VALUES ('3', '王五');
四、DML 1、基本语法 -- DML UPDATE student s SET s.name = '张三' WHERE id = 1;
2、变相 -- DML,0.015s UPDATE student s SET s.name = ( SELECT b.name FROM boy b WHERE s.id = b.id AND s.name != b.name ) WHERE EXISTS ( SELECT 1 FROM boy b WHERE s.id = b.id AND s.name != b.name );
3、快速游标法 -- DML,0.014s BEGIN FOR cur IN ( SELECT s.id sid, b.name bname FROM student s, boy b WHERE s.id = b.id AND s.name != b.name AND s.sex = 'boy' ) loop UPDATE student s SET s.name = cur.bname WHERE s.id = cur.sid; END loop ; END ;
4、内联视图法(inline View) -- DML,0.019s UPDATE ( SELECT s.name sname, b.name bname FROM student s, boy b WHERE s.id = b.id AND s.name != b.name ) SET sname = bname;报错提示:ORA-01779: 无法修改与非键值保存表对应的列 参考资料,从表id必须增加主键约束,且为视图内的where条件: -- DDL ALTER TABLE boy ADD CONSTRAINT pk_id PRIMARY KEY (id);
5、合并法(Merge) MERGE INTO student s USING boy b ON ( s.id = b.id AND s.sex = 'boy' AND s.name != b.name ) WHEN MATCHED THEN UPDATE SET s.name = b.name;报错提示:ORA-38104: 无法更新 ON 子句中引用的列 参考资料,错误原因是条件重复,正确写法: -- DML,0.016s MERGE INTO student s USING boy b ON ( s.id = b.id AND s.sex = 'boy' -- AND s.name != b.name ) WHEN MATCHED THEN UPDATE SET s.name = b.name;
参考资料 【1】《Oracle in和exist的区别》 【2】《Merge无法更新ON子句》 【3】《无法修改与非键值保存表对应的列》 【4】《Oracle的update语句优化》 |
CopyRight 2018-2019 实验室设备网 版权所有 |